package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.GeologicalQueryParams;
import com.css.fxfzypg.modules.baseData.repository.GeologicalRepository;
import com.css.fxfzypg.modules.baseData.entity.GeologicalEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-16 13:59
 */
@Repository
public class GeologicalRepositoryImpl  implements GeologicalRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;



    @Override
    public GeologicalEntity saveGeological(GeologicalEntity geologicalEntity) {
       /* String sql="";
        sql="insert into data_geological_disasters (dgd_reslongitude,dgd_reslatitude,dgd_name,dgd_disaster_type,dgd_impact_peo_num,dgd_impact_house_num,dgd_stability,dgd_danger_level,dgd_createtime,dgd_isdelete) values(?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,
                geologicalEntity.getDgdReslongitude(),
                geologicalEntity.getDgdReslatitude(),
                geologicalEntity.getDgdName(),
                geologicalEntity.getDgdDisasterType(),
                geologicalEntity.getDgdImpactPeoNum(),
                geologicalEntity.getDgdImpactHouseNum(),
                geologicalEntity.getDgdStability(),
                geologicalEntity.getDgdDangerLevel(),
                geologicalEntity.getDgdCreatetime(),
                geologicalEntity.getDgdIsdelete()
                );*/
      /*  SqlParameterSource source = new BeanPropertySqlParameterSource(geologicalEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        System.out.println(i);

        return geologicalEntity;*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into data_geological_disasters(dgd_reslongitude,dgd_reslatitude,dgd_name,dgd_disaster_type,dgd_impact_peo_num,dgd_impact_house_num,dgd_stability,dgd_danger_level,dgd_createtime,dgd_isdelete) values(?, ?, ?, ?, ?, ?, ?, ?,?,?) ";
                //  sql="insert into data_school values(null,?,?,?,?,?,?,?,null ,?)";

                //String sql_sms = "insert into  sms(title,content,date_s,form,sffs,by1,by2,by3) values (?,?,'"+dates+"',?,?,?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String [] {"dgd_id"});
                ps.setFloat(1, geologicalEntity.getDgdReslongitude());
                ps.setFloat(2, geologicalEntity.getDgdReslatitude());
                ps.setString(3, geologicalEntity.getDgdName());
                ps.setString(4, geologicalEntity.getDgdDisasterType());
                ps.setInt(5, geologicalEntity.getDgdImpactPeoNum());
                ps.setInt(6, geologicalEntity.getDgdImpactHouseNum());
                ps.setString(7, geologicalEntity.getDgdStability());
                ps.setString(8, geologicalEntity.getDgdDangerLevel());
                java.sql.Date createDate = new java.sql.Date(geologicalEntity.getDgdCreatetime().getTime());
                ps.setDate(9,  createDate);
                ps.setString(10, geologicalEntity.getDgdIsdelete());
                return ps;
            }
        }, keyHolder);

        int i = keyHolder.getKey().intValue();
        geologicalEntity.setDgdId(i);
        return geologicalEntity;


    }

    @Override
    public Object getGeologicalById(String id) {
        return jdbcTemplate.queryForObject("select u.* from data_geological_disasters u where dgd_id ='"+id+"' ",new BeanPropertyRowMapper<>(GeologicalEntity.class));
    }

    @Override
    public void deleteGeological(List<Integer> ids, Timestamp currentTimestamp) {
        String sql = "update data_geological_disasters set dgd_isdelete = '" + YNEnum.N.toString() + "' ,dgd_updatetime ='"+currentTimestamp+"' " +
                " where dgd_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public Page<GeologicalEntity> getAllGeologicalByParams(GeologicalQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_geological_disasters u ");

          StringBuilder orderSql = new StringBuilder(" order by u.dgd_createtime");

        StringBuilder whereSql = new StringBuilder(" where u.dgd_isdelete = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getDgdReslongitude())) {
            whereSql.append(" and u.dgd_reslongitude like %" + queryParams.getDgdReslongitude() + "%");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDgdReslatitude())) {
            whereSql.append(" and u.dgd_reslatitude like %" + queryParams.getDgdReslatitude() + "%");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<GeologicalEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(GeologicalEntity.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<GeologicalEntity> geologicalEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return geologicalEntities;
    }

    @Override
    public List<GeologicalEntity> exportExcel() {
        return jdbcTemplate.query(" select u.* from data_geological_disasters u  where u.dgd_isdelete='"+YNEnum.Y.toString()+"' ",new BeanPropertyRowMapper<>(GeologicalEntity.class));
    }

    @Override
    public void importExcel(List<GeologicalEntity> geologicalEntities) {
        jdbcTemplate.batchUpdate("insert into data_geological_disasters (dgd_reslongitude,dgd_reslatitude,dgd_name,dgd_disaster_type,dgd_impact_peo_num,dgd_impact_house_num,dgd_stability,dgd_danger_level,dgd_createtime,dgd_isdelete)values(?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1,geologicalEntities.get(i).getDgdReslongitude());
                ps.setFloat(2,geologicalEntities.get(i).getDgdReslatitude());
                ps.setString(3,geologicalEntities.get(i).getDgdName());
                ps.setString(4,geologicalEntities.get(i).getDgdDisasterType());
                //ps.setFloat(5,geologicalEntities.get(i).getDgdImpactPeoNum());
                ps.setObject(5,geologicalEntities.get(i).getDgdImpactPeoNum());
                ps.setObject(6,geologicalEntities.get(i).getDgdImpactHouseNum());
                ps.setString(7,geologicalEntities.get(i).getDgdStability());
                ps.setString(8,geologicalEntities.get(i).getDgdDangerLevel());
                java.sql.Date createDate = new java.sql.Date(geologicalEntities.get(i).getDgdCreatetime().getTime());
                ps.setDate(9,  createDate);
                ps.setString(10,geologicalEntities.get(i).getDgdIsdelete());
            }
            @Override
            public int getBatchSize() {
                return geologicalEntities.size();
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_geological_disasters where county = '" + county + "' and city = '" + city + "' and province = '" + province + "'  or (province ='"+province+"' and city ='' and county ='')  and dgd_isdelete ='"+YNEnum.N+"'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_geological_disasters where city = '" + city + "' and province = '" + province + "' and dgd_isdelete ='"+YNEnum.N+"' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_geological_disasters where province = '" + province + "' and dgd_isdelete ='"+YNEnum.N+"'  ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_geological_disasters where province = ? and (TO_CHAR(dgd_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(dgd_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and dgd_isdelete = '0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
